DROP TABLE temp;

CREATE TABLE temp as

SELECT
    event_time_utc as Event_Date,
   -- TO_CHAR(EVENT_TIME_UTC,'MM/DD/YYYY') as Event_Date,
    --CAST(EVENT_TIME_UTC as DATE) as Event_Date, 
    TO_CHAR(event_time_utc,'HH24') as Hrs,
    TO_CHAR(event_time_utc,'MI') as Mins,
    TO_CHAR(event_time_utc,'SS') as Secs,
    COUNT(SerialNum) as Total_Entries
FROM
    events
GROUP BY
    event_time_utc,
--    TO_CHAR(EVENT_TIME_UTC,'MM/DD/YYYY') ,
--  CAST(EVENT_TIME_UTC as DATE) , 
    TO_CHAR(event_time_utc,'HH24'),
    TO_CHAR(event_time_utc,'MI') ,
    TO_CHAR(event_time_utc,'SS') ;
        

SELECT event_date || ' ' || HRS || ':' || mins || ':' || secs AS Event_Date_Time,
Total_entries 
FROM TEMP
ORDER BY 1;

/********************************************************
Define Summary table
******************************************************/

DROP TABLE Summary;

CREATE TABLE Summary(
start_date DATE,		--'Start Date' 
end_date DATE,			--'End Date' 
server_name VARCHAR(255),	--'Server name' 
server_ip VARCHAR(255),		--'Server IP' 
avg_events_ps int,			--'Average number of events per second' 
med_events_ps int,			--'Median number of events per second' 
peak_events_ps int,			--'Peak number of events per second' 
number_panels int,			--'Number of panels'
number_readers int,		--'Number of readers'
number_inputs int,			--'Number of inputs'
number_outputs int,			--'Number of outputs'
number_personel int,		--'Number of Personnel'
number_badges int			--'Number of Badges'
);


INSERT INTO Summary (start_date, end_date)
SELECT  
    MIN(EVENT_DATE),
    MAX(EVENT_DATE)
--    TO_CHAR(MAX(EVENT_DATE), 'dd-mm-yyyy hh24:mi:ss')
FROM temp;

UPDATE Summary SET server_name =
(SELECT LNLSTRING 
  FROM LNLCONFIG
  WHERE LNLCONFIGID = 240);


/********************************************************

  USING THE SAME DATA SET, GET:
  -MAX AND MIN ENTRIES PER HOUR, 
  -MAX AND MIN ENTRIES PER MINUTE
  -MAX AND MIN ENTRIES PER SECOND 
  FOR EACH DAY IN THE SPECIFIED TIMEFRAME

*********************************************************/

DROP TABLE TotalsPerHour;

CREATE TABLE TotalsPerHour as
SELECT Event_Date,  MAX(A.TotalEntries) AS Max_Per_Hour, MIN(A.TotalEntries) AS Min_Per_Hour
FROM
	(SELECT Event_Date, Hrs, SUM(Total_Entries) AS TotalEntries
	 FROM Temp
	 GROUP BY Event_Date, Hrs) A
GROUP BY 
 Event_Date ,'MM/DD/YYYY';

DROP TABLE TotalsPerMinute;

CREATE TABLE TotalsPerMinute as
SELECT Event_Date , MAX(A.TotalEntries) AS Max_Per_Minute, MIN(A.TotalEntries) AS Min_Per_Minute
FROM
	(SELECT Event_Date, Hrs, Mins, SUM(Total_Entries) AS TotalEntries
	 FROM Temp
	 GROUP BY Event_Date, Hrs, Mins) A
GROUP BY Event_Date, Hrs, Mins;

DROP TABLE TotalsPerSecond;

CREATE TABLE TotalsPerSecond as
SELECT Event_Date,  MAX(A.TotalEntries) AS Max_Per_Second, MIN(A.TotalEntries) AS Min_Per_Second
FROM
	(SELECT Event_Date, Hrs, Mins, Secs, SUM(Total_Entries) as TotalEntries
	 FROM Temp
	 GROUP BY Event_Date, Hrs, Mins, Secs) A
GROUP BY Event_Date, Hrs, Mins, Secs;


SELECT DISTINCT 
                t.Event_Date,
                th.Max_Per_Hour, th.Min_Per_Hour,
                tm.Max_Per_Minute, tm.Min_Per_Minute, 
                ts.Max_Per_Second, ts.Min_Per_Second
FROM Temp t
LEFT OUTER JOIN TotalsPerHour th on t.Event_Date = th.Event_Date
LEFT OUTER JOIN TotalsPerMinute tm on t.Event_Date = tm.Event_Date
LEFT OUTER JOIN TotalsPerSecond ts on t.Event_Date = ts.Event_Date
ORDER BY t.Event_Date;


/********************************************************

  LIST OF EVENT TYPES

*********************************************************/
SELECT EVTYPEID ,EVTDESCR
FROM EVENTYPE
ORDER BY EVTYPEID;

/********************************************************

  COUNT OF ALL ACTIVE BADGES IN THE SYSTEM

*********************************************************/
SELECT 
	COUNT(e.id) as Total_Employee_Records,
	SUM(CASE WHEN UPPER(bt.Name) = 'EMPLOYEE' THEN 1 ELSE 0 END) AS Total_Employee_Badges,
	SUM(CASE WHEN UPPER(bt.Name) = 'VISITOR' THEN 1 ELSE 0 END) AS Total_Visitor_Badges,
	SUM(CASE WHEN UPPER(bt.Name) = 'EMPLOYEE' AND UPPER(s.Name) = 'ACTIVE' THEN 1 ELSE 0 END) AS Active_Employee_Badges,
	SUM(CASE WHEN UPPER(bt.Name) = 'VISITOR' AND UPPER(s.Name) = 'ACTIVE'  THEN 1 ELSE 0 END) AS Active_Visitor_Badges
FROM EMP e
INNER JOIN BADGE b ON e.ID = b.EMPID
INNER JOIN BADGETYP bt ON b.TYPE = bt.ID
INNER JOIN BADGSTAT s ON b.STATUS = s.ID ;


UPDATE Summary SET number_personel
= (SELECT COUNT(*) 
FROM EMP);


UPDATE Summary SET number_badges
= (SELECT COUNT(*) 
        FROM EMP e
        INNER JOIN BADGE b ON e.ID = b.EMPID);

/********************************************************

  COUNT OF PANELS

*********************************************************/

UPDATE Summary SET number_panels
= (SELECT COUNT(*) AS Panel_Count
FROM ACCESSPANE);

/********************************************************

  COUNT OF READERS

*********************************************************/

SELECT COUNT(*) as Total_Readers,
SUM(PAIRMASTER) AS Total_Master,
SUM(PAIRSLAVE) AS Total_Slave,
SUM(CASE WHEN PAIRMASTER = 0 AND PAIRSLAVE = 0 THEN 1 ELSE 0 END) AS Total_Stand_Alone
FROM Reader;

UPDATE Summary SET number_readers
= (SELECT COUNT(*) as Total_Readers FROM Reader
);

/********************************************************

  COUNT OF OUTPUTS

*********************************************************/
UPDATE Summary SET number_outputs =
(SELECT COUNT(*) AS Output_Pin_Count
FROM RELAYOUTPT);

/********************************************************

  COUNT OF INPUTS

*********************************************************/
UPDATE Summary SET number_inputs =
(SELECT COUNT(*) AS Input_Pin_Count
FROM ALARMINPUT);

/********************************************************

  GETS ALL EVENTS THAT OCCURRED AFTER SPECIFIED DATE/TIME.

*********************************************************/
SELECT EVENT_TIME_UTC, EVENTTYPE, ET.EVTDESCR
FROM EVENTS E
INNER JOIN EVENTYPE ET ON  E.EVENTTYPE = ET.EVTYPEID
WHERE EVENT_TIME_UTC >  TO_DATE( '5 Jan 2020', 'DD MON YYYY' );


/********************************************************

  TOTAL NUMBER OF EVENTS

*********************************************************/
SELECT COUNT(*) AS Total_Events
FROM EVENTS E;


/********************************************************

  NUMBER OF EVENTS BY TYPE

*********************************************************/
select ET.EVTDESCR AS Event_Type, count(*) AS Event_Count
FROM EVENTS E
INNER JOIN EVENTYPE ET ON  E.EVENTTYPE = ET.EVTYPEID
GROUP BY ET.EVTDESCR;


/********************************************************

  COUNT OF EMPLOYEES THAT HAVE A PHOTO IMAGE OR THUMBNAIL

*********************************************************/
SELECT COUNT(DISTINCT E.ID) AS Employees_With_Photo
  FROM EMP E
  INNER JOIN MMOBJS M ON E.ID = M.EMPID
  WHERE (TYPE = 0 AND OBJECT = 1) --Photo
  OR (TYPE  = 2 AND OBJECT = 1) --Thumbnail
  ;
  


UPDATE Summary SET peak_events_ps = (SELECT MAX(Max_Per_Second) FROM TotalsPerSecond), 
					avg_events_ps = (SELECT AVG(Max_Per_Second) FROM TotalsPerSecond),
                    med_events_ps = (SELECT(
									 (SELECT MAX(Max_Per_Second) FROM
									   (SELECT Max_Per_Second FROM TotalsPerSecond ORDER BY Max_Per_Second FETCH FIRST 50 PERCENT ROWS ONLY))
									 +
									 (SELECT MIN(Max_Per_Second) FROM
									   (SELECT Max_Per_Second FROM TotalsPerSecond ORDER BY Max_Per_Second DESC FETCH FIRST 50 PERCENT ROWS ONLY) )
									) / 2 FROM dual);
                                    

SELECT 
	start_date AS	"Start Date", 
	end_date AS "End Date", 
	server_name AS "Server name" ,
	avg_events_ps AS "Average number of events per second", 
	med_events_ps AS "Median number of events per second" ,
	peak_events_ps AS "Peak number of events per second" ,
	number_panels AS "Number of panels",
	number_readers AS "Number of readers",
	number_inputs AS "Number of inputs",
	number_outputs AS "Number of outputs",
	number_personel AS "Number of Personnel",
	number_badges AS "Number of Badges"
FROM Summary